using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;


namespace XKPlatform.Common
{
    #region QuerySuite
    public class Search
    {
        public string Order { set; get; }
        public int? Page { set; get; }
        public int? Limit { set; get; }
        public string SearchWhere { set; get; }
    }
    public class SearchWhere
    {
        public string SearchKey { set; get; }
        public string SearchValue { set; get; }
        public string Condition { set; get; }
        public string Compare { set; get; }
    }
    /// <summary>
    /// 查询套件封装,与前端querySuite组件对应。
    /// 说明：项目根据需要扩展。
    /// 
    /// 需要重构：
    /// 重构1、支持mssql,mysql,oracle；
    /// </summary>
    public class QuerySuite
    {
        public string From { set; get; }
        public bool AddWhere = true;
        public string Fields { set; get; }
        public string Order { set; get; }
        public int? StartRow { set; get; }
        public int? EndRow { set; get; }

        public string Cte { set; get; }
        public string Temp { set; get; }
        public string Group { set; get; }
        public List<SearchWhere> CustomList = new List<SearchWhere>();

        List<string> _filter = new List<string>();
        List<SqlParameter> _sqlParams = new List<SqlParameter>();

        public static string GetTableFieldsSql<T>(string[] exclude) where T : class
        {
            var props = typeof(T).GetProperties();

            List<string> keys = new List<string>();

            foreach (PropertyInfo pi in props)
            {
                if (!((IList)exclude).Contains(pi.Name))
                {
                    keys.Add(pi.Name);
                }
            }

            return string.Join(",", keys);
        }
        public static string GetTableFieldsSql<T>(string tableName, string[] exclude) where T : class
        {
            var props = typeof(T).GetProperties();

            List<string> keys = new List<string>();

            foreach (PropertyInfo pi in props)
            {
                if (!((IList)exclude).Contains(pi.Name))
                {
                    keys.Add(tableName + "." + pi.Name);
                }
            }

            return string.Join(",", keys);
        }

        public static string DsCount(DataSet ds)
        {
            return ds.Tables.Count > 1 ? ds.Tables[1].Rows.Count > 0 ? ds.Tables[1].Rows[0][0].ToString() : ds.Tables[0].Rows.Count.ToString() : ds.Tables[0].Rows.Count.ToString();
        }

        public QuerySuite() { }
        public QuerySuite(string from)
        {
            From = from;
        }
        public QuerySuite(string from, string fields)
        {
            From = from;
            Fields = fields;
        }
        public QuerySuite(string from, string fields, string order, bool addWhere = true)
        {
            From = from;
            Fields = fields;
            Order = order;
            AddWhere = addWhere;
        }
        public QuerySuite(string from, string fields, Search serch)
        {
            From = from;
            Fields = fields;
            setSearch(serch);
        }
        public QuerySuite(string from, Search serch)
        {
            From = from;
            setSearch(serch);
        }
        private void setSearch(Search serch)
        {
            if (!string.IsNullOrEmpty(serch.Order))
                Order = serch.Order;
            if (serch.Limit != null)
            {
                StartRow = (serch.Page.Value - 1) * serch.Limit.Value + 1;
                EndRow = StartRow + serch.Limit.Value - 1;
            }

            if (!string.IsNullOrEmpty(serch.SearchWhere))
            {
                var list = JsonConvert.DeserializeObject<List<SearchWhere>>(serch.SearchWhere);
                foreach (SearchWhere item in list)
                {
                    //if (!string.IsNullOrEmpty(item.Compare) && !string.IsNullOrEmpty(item.Condition) && !string.IsNullOrEmpty(item.SearchValue))
                    //    AddParam(item.Compare, !string.IsNullOrEmpty(item.Condition) ? item.Condition : "=", item.SearchValue);
                    //else if(!string.IsNullOrEmpty(item.SearchValue))
                    //    CustomList.Add(item);

                    if (!string.IsNullOrEmpty(item.Condition))
                        AddParam(item.Compare, string.IsNullOrEmpty(item.SearchValue) ? "null" : item.Condition, item.SearchValue);
                    else
                        CustomList.Add(item);
                }

            }
        }
        public void AddFilter(string sql)
        {
            _filter.Add(sql);
        }
        public void AddParam(string sql, SqlParameter para)
        {
            _filter.Add(sql);//[para.ParameterName] = sql;
            if (_sqlParams.FindAll(x => x.ParameterName == para.ParameterName).Count() > 0) return;
            _sqlParams.Add(para);
        }
        public void AddParam(string sql, params SqlParameter[] para)
        {
            _filter.Add(sql);//[para.ParameterName] = sql;
            if (para == null) return;
            foreach (var item in para)
            {
                if (_sqlParams.FindAll(x => x.ParameterName == item.ParameterName).Count() == 0)
                    _sqlParams.Add(item);
            }
        }
        public void AddParam(string ParameterName, object ParameterValue)
        {
            if (_sqlParams.FindAll(x => x.ParameterName == ParameterName).Count() > 0) return;
            _sqlParams.Add(new SqlParameter("@" + ParameterName, ParameterValue));
        }
        public void AddParam(string filed, string type, object value)
        {
            //字段为空
            //if (string.IsNullOrEmpty(filed) || string.IsNullOrEmpty(value.ToString())) return;

            //条件已经存在
            string parameter = "@S_" + filed.Replace(".", "_").Replace(",", "_");
            var paramsCount = _sqlParams.FindAll(x => x.ParameterName.IndexOf(parameter) > -1).Count();
            if (paramsCount > 0)
                parameter = parameter + "_" + paramsCount;

            var fields = filed.Split(',');
            string orSql = " and ({0})";
            string outerSql = " and {0} {1} {2}";

            switch (type)
            {
                case "like":
                    outerSql = " and {0} {1} '%'+{2}+'%'";
                    break;
                case "inid":
                    outerSql = " and ',' + {2} + ',' like '%,' + Cast({0} As nvarchar(36)) + ',%' ";
                    break;
                case "notinid":
                    outerSql = " and ',' + {2} + ',' not like '%,' + Cast({0} As nvarchar(36)) + ',%' ";
                    break;
                case "inchar":
                    outerSql = " and ',' + {2} + ',' like '%,' + cast({0} as nvarchar(100)) + ',%' ";
                    break;
                case "notinchar":
                    outerSql = " and ',' + {2} + ',' not like '%,' + cast({0} as nvarchar(100)) + ',%' ";
                    break;
                case "include":
                    outerSql = " and ',' + {0} + ',' like '%,' + {2} + ',%' ";
                    break;
                case "charin":
                    outerSql = " and charindex(cast({0} as nvarchar(100)),{2})>0 ";
                    break;
                case "notcharin":
                    outerSql = " and charindex(cast({0} as nvarchar(100)),{2})<0 ";
                    break;
                case "notinclude":
                    outerSql = " and ',' + {0} + ',' not like '%,' + {2} + ',%' ";
                    break;
                case "in":
                    outerSql = " and {0} {1} ({2})";
                    break;
                case "notin":
                    outerSql = " and {0} not in ({2})";
                    break;
                case "null":
                    outerSql = " and {0} is null";
                    break;
                case "notnull":
                    outerSql = " and {0} is not null";
                    break;
                case "getday":
                    outerSql = " and CONVERT(varchar(10),{0},120) >= {2}";
                    break;
                case "letday":
                    outerSql = " and CONVERT(varchar(10),{0},120) <= {2}";
                    break;
                default:
                    break;

            }
            if (fields.Length == 1)
                _filter.Add(string.Format(outerSql, filed, type, parameter));//[parameter] = string.Format(outerSql, filed, type, parameter);
            else
            {
                for (int i = 0; i < fields.Length; i++)
                {
                    if (i < fields.Length - 1)
                        orSql = string.Format(orSql, string.Format(outerSql.Replace(" and ", ""), fields[i], type, parameter) + " or {0}");
                    else
                        orSql = string.Format(orSql, string.Format(outerSql.Replace(" and ", ""), fields[i], type, parameter));
                }
                _filter.Add(orSql);//[parameter] = orSql;
            }
            _sqlParams.Add(new SqlParameter(parameter, value));
        }
        public SqlParameter[] Params { get { return _sqlParams.ToArray(); } }

        public string FromSql
        {
            get
            {
                if (AddWhere != false && !From.Substring(From.LastIndexOf(')') > 0 ? From.LastIndexOf(')') : 0).Contains("where") && !From.Substring(0, From.IndexOf('(') > 0 ? From.IndexOf('(') : 0).Contains("where")) From += " where 1=1";

                StringBuilder sbStr = new StringBuilder(From);
                foreach (var e in _filter)
                {
                    sbStr.AppendLine(e);
                }

                return sbStr.ToString();
            }
        }
        public string DeleteSql
        {
            get
            {
                return string.Format(@"delete from {0}", FromSql);
            }
        }

        public string countSql
        {
            get
            {
                return string.Format(@"select count (1) from {0}", FromSql);
            }
        }

        public string SelectFields
        {
            get
            {
                return !string.IsNullOrEmpty(Fields) ? Fields : "*";
            }
        }

        public string QuerySql
        {
            get
            {
                if (!string.IsNullOrEmpty(Order))
                    return string.Format(@"{5} {3} select {1},row_number()over(order by {2}) RowNumber from {0} {4} order by {2}", FromSql, SelectFields, Order, Cte, Group, Temp);
                else
                    return string.Format(@"{4} {2} select {1} from {0} {3}", FromSql, SelectFields, Cte, Group, Temp);
            }
        }

        public string SelectSql
        {
            get
            {
                if (!string.IsNullOrEmpty(Order))
                    return string.Format(@"{5} {3} select {1} from {0} {4} order by {2}", FromSql, SelectFields, Order, Cte, Group, Temp);
                else
                    return string.Format(@"{4} {2} select {1} from {0} {3}", FromSql, SelectFields, Cte, Group, Temp);
            }
        }

        public string LimitSql
        {
            get
            {
                if (!string.IsNullOrEmpty(Order) && StartRow != null)
                    return string.Format(@"{7} {5} select * from (select {1},row_number()over(order by {2}) RowNumber from {0} {6}) t where RowNumber between {3} and {4};{5}  select count(1) from {0} {6}", FromSql, SelectFields, Order, StartRow, EndRow, Cte, Group, Temp);
                else
                    return SelectSql;
            }
        }


        public static List<Dictionary<string, object>> ToDictionary(DataTable dt, string filter = "")
        {
            List<Dictionary<string, object>> dicList = new List<Dictionary<string, object>>();
            foreach (DataRow dr in dt.Select(filter))
            {
                Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                {
                    dic[dc.ColumnName] = dr[dc.ColumnName] == DBNull.Value ? "" : dr[dc.ColumnName];
                }
                dicList.Add(dic);
            }
            return dicList;
        }
        public static List<Dictionary<string, object>> ToDictionary(DataTable data, string parentKey, string primaryKey, string propertyName = "children")
        {
            return ToDictionary(data, parentKey, "", primaryKey, propertyName);
        }
        public static List<Dictionary<string, object>> ToDictionary(DataTable data, string parentKey, string parentValue, string primaryKey, string propertyName)
        {
            List<Dictionary<string, object>> dicList = new List<Dictionary<string, object>>();

            DataRow[] rows = data.Select(string.Format("{0}='{1}'", parentKey, parentValue));
            if (string.IsNullOrEmpty(parentValue))
                rows = data.Select(string.Format("{0} is null or {0}='' ", parentKey));
            foreach (DataRow dr in rows)
            {
                Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn dc in data.Columns)
                {
                    dic[FormatKey(dc.ColumnName)] = dr[dc.ColumnName] == DBNull.Value ? "" : dr[dc.ColumnName];
                }
                dic[FormatKey(propertyName)] = ToDictionary(data, parentKey, Convert.ToString(dr[primaryKey]), primaryKey, propertyName);
                dicList.Add(dic);
            }
            return dicList;
        }
        public static List<Dictionary<string, object>> ToDictionary<T>(List<T> data, string parentKey, string primaryKey, string propertyName = "children")
        {
            return ToDictionary(data, parentKey, "", primaryKey, propertyName);
        }
        public static List<Dictionary<string, object>> ToDictionary<T>(List<T> data, string parentKey, string parentValue, string primaryKey, string propertyName)
        {
            List<Dictionary<string, object>> dicList = new List<Dictionary<string, object>>();

            var list = data.FindAll(x =>
            {
                var xplist = x.GetType().GetProperties();
                foreach (var p in xplist)
                {
                    if (p.Name.ToLower() == parentKey.ToLower())
                        return Convert.ToString(p.GetValue(x)) == Convert.ToString(parentValue);
                }
                return false;
            });
            foreach (var dr in list)
            {
                var plist = dr.GetType().GetProperties();
                PropertyInfo pkey = null;
                Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (var p in plist)
                {
                    dic[FormatKey(p.Name)] = p.GetValue(dr);
                    if (p.Name.ToLower() == primaryKey.ToLower()) pkey = p;
                }
                dic[FormatKey(propertyName)] = ToDictionary(data, parentKey, Convert.ToString(pkey.GetValue(dr)), primaryKey, propertyName);
                dicList.Add(dic);
            }
            return dicList;
        }
        private static string FormatKey(string msg)
        {

            int lastUpper = 0;
            char[] msgArr = msg.ToCharArray();

            for (int i = 0; i < msgArr.Length; i++)
            {
                bool isUpper = (msgArr[i] >= 'A' && msgArr[i] <= 'Z');
                if (isUpper && lastUpper == i - 1)
                    msgArr[i] = msgArr[i].ToString().ToLower()[0];
                if (isUpper) lastUpper = i;
            }
            if (msgArr.Length > 0) msgArr[0] = msgArr[0].ToString().ToLower()[0];
            return new StringBuilder().Append(msgArr).ToString();

            //char[] msgArr = msg.ToCharArray();
            //char[] result = msg.ToCharArray();
            //for (int i = 0; i < msgArr.Length; i++)
            //{
            //    if ((i == 0 || i == msgArr.Length - 1) && msgArr[i] >= 'A' && msgArr[i] <= 'Z')
            //        result[i] = msgArr[i].ToString().ToLower()[0];
            //    else if (i + 1 < msgArr.Length && msgArr[i] >= 'A' && msgArr[i] <= 'Z' && msgArr[i - 1] >= 'A' && msgArr[i - 1] <= 'Z')
            //        result[i] = msgArr[i].ToString().ToLower()[0];
            //}
            //return new StringBuilder().Append(result).ToString();
        }
    }

    #endregion

}